<?php
/**
 * 区域房源成交统计
 */

namespace app\crontab\controller\always;


use custom\Log;
use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\Db;

class HouseStatisticsAreaMonth extends Command {


    protected function configure() {
        $this->setName('HouseStatisticsAreaMonth');
    }

    protected function execute(Input $input, Output $output) {
        $this->initial();
    }

    public function initial() {
        // 关闭日志写入
        \think\facade\Log::close();
        echo "starting\n";
        date_default_timezone_set('PRC');
        $citys = config('global.house_city_all');
        $areas = Db::name("region")->where('p_code', 'in', $citys)->field('city_code')->select();
        $areas = array_column($areas, 'city_code');
        $areas = join(',', $areas);
        $start_time = strtotime(date('Y-m', strtotime('-1 month')) . '-01');
        $end_time = strtotime(date('Y-m', time()) . '-01');
        //成交时间
        $where1 = "and erp_house_auction.deal_time >= $start_time and erp_house_auction.deal_time <$end_time";
        //拍卖时间
        $where2 = "and erp_house_auction.start_time < $end_time and erp_house_auction.end_time >= $start_time";
        //上个月进行中总套数
        $jinxing = "(select count(erp_house.id) from erp_house join erp_house_auction on erp_house_auction.id = erp_house.auction_id where erp_house.city_code = erp_region.city_code $where2 and erp_house_auction.auction_status = 2  ) as jinxing";
        //上个月待开始总套数
        $daikaishi = "(select count(erp_house.id) from erp_house join erp_house_auction on erp_house_auction.id = erp_house.auction_id where erp_house.city_code = erp_region.city_code $where2 and erp_house_auction.auction_status = 1  ) as daikaishi";
        //上个月成交总套数
        $deal_total_number = "(select count(erp_house.id) from erp_house join erp_house_auction on erp_house_auction.id = erp_house.auction_id where erp_house.city_code = erp_region.city_code and erp_house_auction.auction_status=3 $where1) as deal_total_number";
        //成交金额
        $deal_money = "(select sum(erp_house_auction.final_price) from erp_house join erp_house_auction on erp_house_auction.id = erp_house.auction_id where erp_house.city_code = erp_region.city_code and erp_house_auction.auction_status=3 $where1) as deal_money";
        //竞价人数
        $bidders_number = "(select sum(erp_house_auction.enroll_num) from erp_house join erp_house_auction on erp_house_auction.id = erp_house.auction_id where erp_house.city_code = erp_region.city_code $where1) as bidders_number";
        //折扣比
        $discount_ratio = "(select avg(erp_house_auction.discount) from erp_house join erp_house_auction on erp_house_auction.id = erp_house.auction_id where erp_house.city_code = erp_region.city_code and erp_house_auction.discount > 0 $where1) as discount_ratio";
        //总面积
        $total_area = "(select sum(erp_house.build_area) from erp_house join erp_house_auction on erp_house_auction.id = erp_house.auction_id where erp_house.city_code = erp_region.city_code and erp_house_auction.auction_status=3 $where1 ) as total_area";
        $data = Db::query("select city_code,p_code,name,{$jinxing},{$daikaishi},{$deal_total_number},{$deal_money},{$bidders_number},{$discount_ratio},{$total_area} from erp_region where city_code in ({$areas})");

        foreach ($data as $k2 => $v2) {
            $jinxing_ = $v2['jinxing'] ?? 0;//进行中
            $daikaishi_ = $v2['daikaishi'] ?? 0;//待开始
            $deal_total_number = $v2['deal_total_number'] ?? 0;//成交量

            //总套数
            $total_house = $jinxing_ + $daikaishi_ + $deal_total_number;

            $deal_money = $v2['deal_money'] ?? 0;

            $_deal_rate = empty($total_house) ? 0 : ($deal_total_number / $total_house) * 100;
            $deal_rate = (int)$_deal_rate;

            $deal_price = empty($v2['total_area']) ? 0 : $deal_money / ($v2['total_area'] / 10000);
            $insert = [
                'city_code'         => $v2['p_code'],    //城市code
                'adcode'            => $v2['city_code'],  //区域code
                'total_number'      => $total_house,//总套数
                'deal_total_number' => $deal_total_number, //成交总套数
                'deal_rate'         => $deal_rate,//成交率
                'deal_money'        => $deal_money,//成交金额（单位分）
                'deal_price'        => (int)$deal_price,//成交单价（单位分）
                'bidders_number'    => $v2['bidders_number'] ?? 0,//竞价人数
                'discount_ratio'    => (int)($v2['discount_ratio'] * 10),//折扣比
                'count_time'        => strtotime(date('Y-m', strtotime('-1 month')) . '-01'),
            ];
            $res[] = $insert;
        }
        $total = count($res);
        if (Db::name('house_statistics_area_month')
            ->where('count_time', strtotime(date('Y-m', strtotime('-1 month')) . '-01'))
            ->select()) {
            Log::write("上个月已经统计过了", 'house_statistics_area_month.log');
            die;
        }
        $suc = Db::name('house_statistics_area_month')->data($res)->limit(100)->insertAll();
        echo 'success->' . $suc . PHP_EOL;
        echo 'total->' . $total . PHP_EOL;
        echo "end" . PHP_EOL;
        $res = json_encode($res);
        Log::write("success->$suc;total->$total;res:$res", 'house_statistics_area_month.log');
    }

}